SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[usp_TimKiemTourNuocNgoaiCuThe]
	@NoiDi nvarchar(20),
	@NoiDen nvarchar(20),
	@Gia1 int,
	@Gia2 int,
	@Year char(4),
	@Month char(2),
	@Day char(2)
AS
BEGIN
	IF @Year = '' AND @Month = '' AND @Day = ''
	BEGIN
		SELECT *
		FROM TOUR
		WHERE	MA_TOUR LIKE 'NN%' AND NOI_KH = @NoiDi AND
  				TEN_TOUR LIKE N'%'+@NoiDen+'%' AND TRANG_THAI = 'MO' AND
				GIA BETWEEN @Gia1 AND @Gia2 AND 
				NGAY_DI >= GETDATE() 
		ORDER BY NGAY_DI ASC     
	END
	
	ELSE
	
	IF @Year = '' AND @Month = ''
	BEGIN
		SELECT *
		FROM TOUR
		WHERE	MA_TOUR LIKE 'NN%' AND NOI_KH = @NoiDi AND
  				TEN_TOUR LIKE N'%'+@NoiDen+'%' AND TRANG_THAI = 'MO' AND
				GIA BETWEEN @Gia1 AND @Gia2 AND 
				DATEPART(mm, NGAY_DI) >= DATEPART(mm, GETDATE()) AND
				DATEPART(yy, NGAY_DI) >= DATEPART(yy, GETDATE()) AND
				DATEPART(dd, NGAY_DI) = @Day
		ORDER BY NGAY_DI ASC          
	END

	ELSE
  
	IF @Year = '' AND @Day = ''
	BEGIN
		SELECT *
		FROM TOUR
		WHERE	MA_TOUR LIKE 'NN%' AND NOI_KH = @NoiDi AND
  				TEN_TOUR LIKE N'%'+@NoiDen+'%' AND TRANG_THAI = 'MO' AND
				GIA BETWEEN @Gia1 AND @Gia2 AND 
				DATEPART(dd, NGAY_DI) >= DATEPART(dd, GETDATE()) AND
				DATEPART(yy, NGAY_DI) >= DATEPART(yy, GETDATE()) AND
				DATEPART(mm, NGAY_DI) = @Month
		ORDER BY NGAY_DI ASC     
	END

	ELSE
  
 	IF @Month = '' AND @Day = ''
	BEGIN
		SELECT *
		FROM TOUR
		WHERE	MA_TOUR LIKE 'NN%' AND NOI_KH = @NoiDi AND
  				TEN_TOUR LIKE N'%'+@NoiDen+'%' AND TRANG_THAI = 'MO' AND
				GIA BETWEEN @Gia1 AND @Gia2 AND 
				DATEPART(dd, NGAY_DI) >= DATEPART(dd, GETDATE()) AND
				DATEPART(mm, NGAY_DI) >= DATEPART(mm, GETDATE()) AND
				DATEPART(yy, NGAY_DI) = @Year
		ORDER BY NGAY_DI ASC     
	END  

	ELSE
  
   	IF @Year = ''
	BEGIN
		SELECT *
		FROM TOUR
		WHERE	MA_TOUR LIKE 'NN%' AND NOI_KH = @NoiDi AND
  				TEN_TOUR LIKE N'%'+@NoiDen+'%' AND TRANG_THAI = 'MO' AND
				GIA BETWEEN @Gia1 AND @Gia2 AND 
				DATEPART(yy, NGAY_DI) >= DATEPART(yy, GETDATE()) AND
				DATEPART(dd, NGAY_DI) = @Day AND
				DATEPART(mm, NGAY_DI) = @Month
		ORDER BY NGAY_DI ASC     
	END    

	ELSE
  
   	IF @Month = ''
	BEGIN
		SELECT *
		FROM TOUR
		WHERE	MA_TOUR LIKE 'NN%' AND NOI_KH = @NoiDi AND
  				TEN_TOUR LIKE N'%'+@NoiDen+'%' AND TRANG_THAI = 'MO' AND
				GIA BETWEEN @Gia1 AND @Gia2 AND 
				DATEPART(mm, NGAY_DI) >= DATEPART(mm, GETDATE()) AND
				DATEPART(dd, NGAY_DI) = @Day AND
				DATEPART(yy, NGAY_DI) = @Year
		ORDER BY NGAY_DI ASC    
	END    

	ELSE
  
   	IF @Day = ''
	BEGIN
		SELECT *
		FROM TOUR
		WHERE	MA_TOUR LIKE 'NN%' AND NOI_KH = @NoiDi AND
  				TEN_TOUR LIKE N'%'+@NoiDen+'%' AND TRANG_THAI = 'MO' AND
				GIA BETWEEN @Gia1 AND @Gia2 AND 
				DATEPART(dd, NGAY_DI) >= DATEPART(dd, GETDATE()) AND
				DATEPART(yy, NGAY_DI) = @Year AND
				DATEPART(mm, NGAY_DI) = @Month 
		ORDER BY NGAY_DI ASC    
	END    
	
	ELSE
  
	BEGIN
 		SELECT *
		FROM TOUR
		WHERE	MA_TOUR LIKE 'NN%' AND NOI_KH = @NoiDi AND
  				TEN_TOUR LIKE N'%'+@NoiDen+'%' AND TRANG_THAI = 'MO' AND
				GIA BETWEEN @Gia1 AND @Gia2 AND 
				DATEPART(dd, NGAY_DI) = @Day AND
				DATEPART(yy, NGAY_DI) = @Year AND
				DATEPART(mm, NGAY_DI) = @Month  
		ORDER BY NGAY_DI ASC 
	END             
END

GO
